﻿Imports System.Data
Imports System.Data.OleDb
Imports System.Web.Configuration
Imports System.Text.RegularExpressions
Partial Class songEditor
    Inherits System.Web.UI.Page
    Public songID As Integer

    ''' <summary>
    ''' Method that toggles the view of the "chord chooser" multiview
    ''' </summary>
    ''' <param name="sender">button_toggleChordChooser</param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub toggleChordChooser_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button_toggleChordChooser.Click
        If Me.multiview_chordChooser.ActiveViewIndex = 0 Then 'the custom chord chooser view is displayed currently
            Me.multiview_chordChooser.ActiveViewIndex = 1 'toggle multiview to show the standard chord chooser view
            Me.button_toggleChordChooser.Text = "Switch to custom chord chooser" 'change toggle button text accordingly
        Else 'the standard chord chooser view is displayed currently
            Me.multiview_chordChooser.ActiveViewIndex = 0 'toggle multiview to show the custom chord chooser view
            Me.button_toggleChordChooser.Text = "Switch to standard chord chooser" 'change toggle button text accordingly
        End If

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'check that user is authenticated - redirect to home page if not
        If Not Page.User.Identity.IsAuthenticated Then
            'no ID in query string so redirect to home page
            Response.Redirect("~/home.aspx")
        End If

        'set me.songID to ID if present in the query string, otherwise set to 0 (indicating that this is a brand new song)
        If Request.QueryString("ID") Is Nothing Then
            Me.songID = 0
        Else
            If Not Regex.IsMatch(Request.QueryString("ID"), "^[0-9]{1,}$") Then
                'ID in query string is not an integer so redirect to home page
                Response.Redirect("~/home.aspx")
            Else
                'ID is an integer, so set me.songID variable to ID
                Me.songID = CInt(Request.QueryString("ID"))
            End If
        End If

        'initialise connection object
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString
        'declare reader
        Dim myReader As OleDbDataReader

        '-------------------------------------------
        'Populate common chords panel
        '-------------------------------------------
        'create command objects
        Dim myCommand_queryChords = New OleDbCommand("SELECT pattern, code, alt from commonChords;")
        Dim myImageButton As ImageButton
        Try
            'open connection
            myConnection.Open()
            'set connection property of command objects
            myCommand_queryChords.Connection = myConnection
            'declare readers
            Dim myReader_chords As OleDbDataReader
            'execute readers
            myReader_chords = myCommand_queryChords.ExecuteReader
            Do While myReader_chords.Read()
                'create asp:imagebutton control
                myImageButton = New ImageButton
                'set URL to render chord
                myImageButton.ImageUrl = "~/chord.aspx?size=100&chordPattern=" & _
                    myReader_chords("pattern") & "&chordTitle=" & myReader_chords("alt")
                'add "code" attribute
                myImageButton.Attributes.Add("code", myReader_chords("code"))
                AddHandler myImageButton.Click, AddressOf Me.chord_Click
                Me.div_chordList.Controls.Add(myImageButton)
            Loop
            'close connection
            myConnection.Close()
        Catch ex As Exception
            'redirect to error message page
            Response.Redirect("~/error.htm")
        Finally
            myConnection.Close()
        End Try

        If Not Page.IsPostBack Then 'we are initiating a new editing session, and need to populate the page
            '-------------------------------------------
            'populate drop down lists for band and genre
            '-------------------------------------------
            'create command objects
            Dim myCommand_queryBands = New OleDbCommand("SELECT * from bands ORDER BY bandName;")
            Dim myCommand_queryGenres = New OleDbCommand("SELECT * from genres ORDER BY genreName;")
            Try
                'open connection
                myConnection.Open()
                'set connection property of command objects
                myCommand_queryBands.Connection = myConnection
                myCommand_queryGenres.Connection = myConnection
                'declare readers
                Dim myReader_bands As OleDbDataReader
                Dim myReader_genres As OleDbDataReader
                'execute readers
                myReader_bands = myCommand_queryBands.ExecuteReader
                myReader_genres = myCommand_queryGenres.ExecuteReader
                'set data source, data value and data text field properties to prepare for data binding to fill drop down list boxes
                Me.dropDownList_bands.DataSource = myReader_bands
                Me.dropDownList_bands.DataValueField = "ID"
                Me.dropDownList_bands.DataTextField = "bandName"
                Me.dropDownList_genres.DataSource = myReader_genres
                Me.dropDownList_genres.DataValueField = "ID"
                Me.dropDownList_genres.DataTextField = "genreName"
                'data bind controls
                Me.dropDownList_bands.DataBind()
                Me.dropDownList_genres.DataBind()
                'close connection
                myConnection.Close()
            Catch ex As Exception
                'redirect to error message page
                Response.Redirect("~/error.htm")
            Finally
                myConnection.Close()
            End Try

            '-------------------------------------------
            'populate song details, if me.songID>0 
            '-------------------------------------------
            If Me.songID > 0 Then
                'declare and instantiate command object
                Dim myCommand_querySongDetails = New OleDbCommand("SELECT title, lyricsPlusChords, genreID, bandID FROM songDetails WHERE ID=" & Me.songID & ";")
                Try
                    'open connection
                    myConnection.Open()
                    'set connection property of command object
                    myCommand_querySongDetails.Connection = myConnection
                    'execute readers
                    myReader = myCommand_querySongDetails.ExecuteReader
                    'assign values 
                    myReader.Read()
                    Me.textBox_songTitle.Text = myReader("title")
                    Me.textbox_songLyrics.Text = myReader("lyricsPlusChords").ToString
                    Me.dropDownList_genres.SelectedValue = myReader("genreID").ToString
                    Me.dropDownList_bands.SelectedValue = myReader("bandID").ToString
                    'close connection
                    myConnection.Close()
                Catch ex As Exception
                    'redirect to error message page
                    Response.Redirect("~/error.htm")
                Finally
                    myConnection.Close()
                End Try
            End If
        End If
        'render preview
        Me.div_preview.InnerHtml = renderer.renderSong(Replace(Me.textbox_songLyrics.Text, Environment.NewLine, "<br/>"))
    End Sub

    ''' <summary>
    ''' Sub to handle click events on chord symbols, to add codes to the chord wiki and refresh preview pane
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub Button_addChord_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_addChord.Click
        Dim code As String
        'WARNING - workaround used to get the ID, as ASP.NET prefixes ids with naming controls
        'workaround may fail if ASP.NET starts using different prefixes !!!
        code = "[" & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string6") & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string5") & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string4") & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string3") & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string2") & _
            Request.Form("ctl00$ContentPlaceHolder1$" & "string1") & _
        Textbox_chordName.Text & "]"
        Me.textbox_songLyrics.Text = Me.textbox_songLyrics.Text & code
        'update preview pane
        Me.div_preview.InnerHtml = renderer.renderSong(Replace(Me.textbox_songLyrics.Text, Environment.NewLine, "<br/>"))
    End Sub
    ''' <summary>
    ''' Sub that functions as an event handler to handle clicks on chord images to add the chord code to the wiki text
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub chord_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
        Dim myChord = CType(sender, ImageButton) 'get sender and cast to ImageButton
        Me.textbox_songLyrics.Text = Me.textbox_songLyrics.Text & myChord.Attributes.Item("code") 'get code attribute
        Me.div_preview.InnerHtml = renderer.renderSong(Replace(Me.textbox_songLyrics.Text, Environment.NewLine, "<br/>"))
    End Sub

    Protected Sub Button_newGenre_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_newGenre.Click
        Dim newGenre As String = Trim(Me.TextBox_newGenre.Text)
        If newGenre = "" Then Exit Sub

        'set up connection and connection string
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString

        'set up command objects
        Dim myCommand_addGenre = New OleDbCommand("INSERT INTO genres (genreName) VALUES (?);")
        Dim myCommand_getID = New OleDbCommand("SELECT @@Identity;")
        Dim myCommand_checkGenreIsNew = New OleDbCommand("SELECT ID from genres WHERE genreName=?")

        Try
            'open connection
            myConnection.Open()
            'set connection property of command objects
            myCommand_addGenre.Connection = myConnection
            myCommand_getID.Connection = myConnection
            myCommand_checkGenreIsNew.Connection = myConnection

            'run query to check whether genre is already present
            myCommand_checkGenreIsNew.Parameters.Add(New OleDbParameter("genreName", newGenre))
            Dim genreID As Integer = myCommand_checkGenreIsNew.ExecuteScalar()
            If genreID > 0 Then
                'genre entered is already present, so clear genre box, select genre in the drop down list and exit sub
                Me.TextBox_newGenre.Text = ""
                Me.dropDownList_genres.SelectedValue = genreID
                myConnection.Close()
                Exit Sub
            End If

            'run query to add genre to genres table
            myCommand_addGenre.Parameters.Add(New OleDbParameter("genreName", newGenre))
            myCommand_addGenre.ExecuteNonQuery()
            'run query to get ID of genre added
            genreID = myCommand_getID.ExecuteScalar()
            'clear genre box, add new genre to the drop down list and select it
            Me.TextBox_newGenre.Text = ""
            Dim newListItem As New ListItem
            newListItem.Value = genreID
            newListItem.Text = newGenre
            Me.dropDownList_genres.Items.Add(newListItem)
            Me.dropDownList_genres.SelectedValue = genreID

            'close connection
            myConnection.Close()
        Catch ex As Exception
            Response.Redirect("~/error.htm")
        End Try
    End Sub

    Protected Sub Button_newBand_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_newBand.Click
        Dim newBand As String = Trim(Me.TextBox_newBand.Text)
        If newBand = "" Then Exit Sub

        'set up connection and connection string
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString

        'set up command objects
        Dim myCommand_addBand = New OleDbCommand("INSERT INTO bands (bandName) VALUES (?);")
        Dim myCommand_getID = New OleDbCommand("SELECT @@Identity;")
        Dim myCommand_checkBandIsNew = New OleDbCommand("SELECT ID from bands WHERE bandName=?")

        Try
            'open connection
            myConnection.Open()
            'set connection property of command objects
            myCommand_addBand.Connection = myConnection
            myCommand_getID.Connection = myConnection
            myCommand_checkBandIsNew.Connection = myConnection

            'run query to check whether Band is already present
            myCommand_checkBandIsNew.Parameters.Add(New OleDbParameter("BandName", newBand))
            Dim BandID As Integer = myCommand_checkBandIsNew.ExecuteScalar()
            If BandID > 0 Then
                'Band entered is already present, so clear Band box, select Band in the drop down list and exit sub
                Me.TextBox_newBand.Text = ""
                Me.dropDownList_bands.SelectedValue = BandID
                myConnection.Close()
                Exit Sub
            End If

            'run query to add Band to Bands table
            myCommand_addBand.Parameters.Add(New OleDbParameter("bandName", newBand))
            myCommand_addBand.ExecuteNonQuery()
            'run query to get ID of Band added
            BandID = myCommand_getID.ExecuteScalar()
            'clear Band box, add new Band to the drop down list and select it
            Me.TextBox_newBand.Text = ""
            Dim newListItem As New ListItem
            newListItem.Value = BandID
            newListItem.Text = newBand
            Me.dropDownList_bands.Items.Add(newListItem)
            Me.dropDownList_bands.SelectedValue = BandID

            'close connection
            myConnection.Close()
        Catch ex As Exception
            Response.Redirect("~/error.htm")
        End Try
    End Sub

    Protected Sub Button_save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_save.Click
        'set up connection and connection string
        Dim myConnection = New OleDbConnection()
        myConnection.ConnectionString = WebConfigurationManager.ConnectionStrings("yobboGuitar").ConnectionString

        'set up command objects
        Dim myCommand_save As OleDbCommand = Nothing
        Dim myCommand_getID As OleDbCommand = Nothing

        If Me.songID = 0 Then
            'new song, so commands will be an INSERT query and a query to get ID of new record
            myCommand_save = New OleDbCommand( _
       "INSERT INTO songs (title, lyricsPlusChords, genreID, bandID, rating, numberOfRatings, userID) VALUES (?,?,?,?,?,?,?);")
            myCommand_save.Parameters.Add(New OleDbParameter("title", Me.textBox_songTitle.Text))
            myCommand_save.Parameters.Add(New OleDbParameter("lyricsPlusChords", Me.textbox_songLyrics.Text))
            myCommand_save.Parameters.Add(New OleDbParameter("genreID", Me.dropDownList_genres.SelectedValue))
            myCommand_save.Parameters.Add(New OleDbParameter("bandID", Me.dropDownList_bands.SelectedValue))
            myCommand_save.Parameters.Add(New OleDbParameter("rating", 0))
            myCommand_save.Parameters.Add(New OleDbParameter("numberOfRatings", 0))
            myCommand_save.Parameters.Add(New OleDbParameter("userID", util.getUserID()))
            myCommand_getID = New OleDbCommand("SELECT @@Identity;")
        Else
            'existing song, so command will be an UPDATE query
            myCommand_save = New OleDbCommand( _
      "UPDATE songs SET title=?, lyricsPlusChords=?, genreID=?, bandID=? WHERE ID=?")
            myCommand_save.Parameters.Add(New OleDbParameter("title", Me.textBox_songTitle.Text))
            myCommand_save.Parameters.Add(New OleDbParameter("lyricsPlusChords", Me.textbox_songLyrics.Text))
            myCommand_save.Parameters.Add(New OleDbParameter("genreID", Me.dropDownList_genres.SelectedValue))
            myCommand_save.Parameters.Add(New OleDbParameter("bandID", Me.dropDownList_bands.SelectedValue))
            myCommand_save.Parameters.Add(New OleDbParameter("ID", Me.songID))
        End If

        Try
            'open connection
            myConnection.Open()
            'set connection property of command objects
            myCommand_save.Connection = myConnection



            'run query to INSERT into or UPDATE songs table
            myCommand_save.ExecuteNonQuery()

            If Me.songID = 0 Then
                'run query to retrieve ID of INSERTed record
                myCommand_getID.Connection = myConnection
                Me.songID = myCommand_getID.ExecuteScalar
            End If

            'close connection
            myConnection.Close()
        Catch ex As Exception
            Response.Redirect("~/error.htm")
        End Try
        'redirect to display song
        Response.Redirect("~/displaySong.aspx?ID=" & Me.songID)
    End Sub

    Protected Sub Button_cancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button_cancel.Click
        If Me.songID = 0 Then
            Response.Redirect("~/home.aspx?ID=")
        Else
            Response.Redirect("~/displaySong.aspx?ID=" & Me.songID)
        End If
    End Sub
End Class
